#!/usr/bin/python
# -*- coding: UTF-8 -*-
"""
Python 3.x
SQLAlchemy 1.4.41

SQLAlchemy 表关系
多对多
"""
from sqlalchemy import Column, Integer, String, Text
from constants import DB_URI
from sqlalchemy import create_engine
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker

engine = create_engine(DB_URI, echo=True)
# 所有的类都要继承自`declarative_base`这个函数生成的基类
Base = declarative_base(engine)
Session = sessionmaker(bind=engine)
session = Session()

from sqlalchemy.orm import relationship
from sqlalchemy import Table
from sqlalchemy import ForeignKey

intermediate_table = Table('teacher_classes', Base.metadata,
                           Column('teacher_id', Integer, ForeignKey('teacher.id')),
                           Column('classes_id', Integer, ForeignKey('classes.id'))
                           )


class Teacher(Base):
    __tablename__ = 'teacher'
    id = Column(Integer, primary_key=True)
    name = Column(String(50))
    classes = relationship('Classes', secondary=intermediate_table, back_populates='teachers')

    def __repr__(self):
        return "<Teacher(id='%s', name='%s')>" % (self.id, self.name)


class Classes(Base):
    __tablename__ = 'classes'
    id = Column(Integer, primary_key=True)
    name = Column(String(50))
    teachers = relationship('Teacher', secondary=intermediate_table, back_populates='classes')

    def __repr__(self):
        return "<Classes(id='%s', name='%s')>" % (self.id, self.name)


# 映射到数据库
Base.metadata.drop_all()
Base.metadata.create_all()

teacher1 = Teacher(name='English Teacher')
teacher2 = Teacher(name='Chinese Teacher')
classes1 = Classes(name='Class 1 of the third grade')
classes2 = Classes(name='Class 2 of the third grade')
classes3 = Classes(name='Class 3 of the third grade')
teacher1.classes = [classes1, classes2]
teacher2.classes = [classes1, classes2, classes3]
classes1.teachers = [teacher1, teacher2]
classes2.teachers = [teacher1, teacher2]
classes3.teachers = [teacher2]
session.add(teacher1)
session.add(teacher2)
session.add(classes1)
session.add(classes2)
session.add(classes3)

session.commit()

tmp_teacher = session.query(Teacher).all()
print(tmp_teacher)
# > [<Teacher(id='1', name='English Teacher')>, <Teacher(id='2', name='Chinese Teacher')>]
for tmp in tmp_teacher:
    print(tmp.name, tmp.classes)
# > English Teacher [<Classes(id='1', name='Class 1 of the third grade')>,
# > <Classes(id='3', name='Class 2 of the third grade')>]
# > Chinese Teacher [<Classes(id='1', name='Class 1 of the third grade')>,
# > <Classes(id='2', name='Class 3 of the third grade')>,
# > <Classes(id='3', name='Class 2 of the third grade')>]

tmp_classes = session.query(Classes).all()
print(tmp_classes)
# > [<Classes(id='1', name='Class 1 of the third grade')>,
# >  <Classes(id='2', name='Class 3 of the third grade')>, <
# >  Classes(id='3', name='Class 2 of the third grade')>]

for tmp in tmp_classes:
    print(tmp.name, tmp.teachers)
# > Class 1 of the third grade [<Teacher(id='1', name='English Teacher')>, <Teacher(id='2', name='Chinese Teacher')>]
# > Class 3 of the third grade [<Teacher(id='2', name='Chinese Teacher')>]
# > Class 2 of the third grade [<Teacher(id='1', name='English Teacher')>, <Teacher(id='2', name='Chinese Teacher')>]

session.close()
